﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Aspose.Cells;

namespace OF.Utility
{
    public class ExcelHelper
    {
        #region 第三方导出Excel
        public static void ExportDataSetToFile(string[] caption, string[] columns, DataTable dt, string fileName)
        {

            if (dt == null || dt.Rows.Count == 0) return;

            if (caption == null || columns == null || caption.Length != columns.Length)
            {
                columns = null;
            }
            //fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
            HttpResponse resp;
            resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            resp.HeaderEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //!important

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = fileName;
            string SheetName = "sheet1";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF xf = doc.NewXF();
            xf.Font.Height = 14 * 20;

            int r = 0;//计算行数
            #region 首行标题
            if (caption != null && caption.Length > 0)
            {
                r++;
                for (int i = 0; i < caption.Length; i++)
                {
                    cells.Add(r, (i + 1), caption[i], xf);
                }
            }
            else
            {
                r++;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    cells.Add(r, (i + 1), dt.Columns[i].Caption.ToString(), xf);
                }
            }
            #endregion

            xf.Font.Height = 12 * 20;

            foreach (DataRow dr in dt.Rows)
            {
                r++;
                if (columns == null || columns.Length == 0)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        object value = dr[i].ToString();
                        try
                        {
                            double d = Convert.ToDouble(value);
                            value = d;
                        }
                        catch
                        {
                        }
                        cells.Add(r, (i + 1), value, xf);
                    }
                }
                else
                {
                    for (int i = 0; i < columns.Length; i++)
                    {
                        if (dt.Columns.Contains(columns[i]))
                        {
                            object value = dr[columns[i]].ToString();
                            try
                            {
                                double d = Convert.ToDouble(value);
                                value = d;
                            }
                            catch
                            {
                            }
                            cells.Add(r, (i + 1), value, xf);
                        }
                        else
                        {
                            cells.Add(r, (i + 1), "");
                        }
                    }
                }
            }

            doc.Send();
            resp.Flush();
            resp.End();


        }


        public static void ExportDataSetToFile(string[] caption, Dictionary<int, string[]> rows, string fileName)
        {

            if (rows.Count == 0) return;
            HttpResponse resp;
            resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            resp.HeaderEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //!important

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = fileName;
            string SheetName = "sheet1";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF xf = doc.NewXF();
            xf.Font.Height = 14 * 20;

            int r = 0;//计算行数
            #region 首行标题
            if (caption != null && caption.Length > 0)
            {
                r++;
                for (int i = 0; i < caption.Length; i++)
                {
                    cells.Add(r, (i + 1), caption[i], xf);
                }
            }
            #endregion

            xf.Font.Height = 12 * 20;

            foreach (var item in rows)
            {
                r++;
                for (int i = 0; i < item.Value.Length; i++)
                {
                    cells.Add(r, (i + 1), item.Value[i], xf);
                }
            }
            //doc.Save(resp.OutputStream);
            doc.Send();
            resp.Flush();
            resp.End();


        }

        public static void ExportDataSetToFile(Dictionary<string, int> masterDic, string[] caption, Dictionary<int, string[]> rows, string fileName)
        {

            // if (rows.Count == 0) return;
            HttpResponse resp;
            resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            resp.HeaderEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //!important

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = fileName;
            string SheetName = "sheet1";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF xf = doc.NewXF();
            xf.Font.Height = 14 * 20;

            int r = 0;//计算行数
            #region 首行标题
            if (masterDic != null && masterDic.Count > 0)
            {
                r++;
                int col = 0;
                foreach (var item in masterDic)
                {
                    col++;
                    cells.Add(r, col, item.Key, xf);
                    int s = item.Value - 1;
                    for (int i = 0; i < s; i++)
                    {
                        col++;
                        cells.Add(r, col, "", xf);

                    }
                }


                int up = 1;
                foreach (var item in masterDic)
                {
                    int s = up + (item.Value == 0 ? 0 : item.Value - 1);
                    cells.Merge(1, 1, up, s);

                    up += item.Value == 0 ? item.Value + 1 : item.Value;

                }
            }
            if (caption != null && caption.Length > 0)
            {
                r++;
                for (int i = 0; i < caption.Length; i++)
                {
                    cells.Add(r, (i + 1), caption[i], xf);
                }
            }
            #endregion

            xf.Font.Height = 12 * 20;

            foreach (var item in rows)
            {
                r++;
                for (int i = 0; i < item.Value.Length; i++)
                {
                    cells.Add(r, (i + 1), item.Value[i], xf);
                }
            }
            //doc.Save(resp.OutputStream);
            doc.Send();
            resp.Flush();
            resp.End();


        }
        #endregion

        /// <summary>
        ///  保存在服务器本地，不包含http请求  liucx
        /// </summary>
        /// <param name="caption"></param>
        /// <param name="rows"></param>
        /// <param name="fileName">文件名</param>
        /// <param name="filePath">文件路径</param>
        public static string ExportDataSetToFileLocal(string[] caption, Dictionary<int, string[]> rows, string fileName, string filePath)
        {

            if (rows.Count == 0) return"";
            if (string.IsNullOrWhiteSpace(filePath))
                filePath = "/excel";

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = fileName;
            string SheetName = "sheet1";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF xf = doc.NewXF();
            xf.Font.Height = 14 * 20;

            int r = 0;//计算行数
            #region 首行标题
            if (caption != null && caption.Length > 0)
            {
                r++;
                for (int i = 0; i < caption.Length; i++)
                {
                    cells.Add(r, (i + 1), caption[i], xf);
                }
            }
            #endregion

            xf.Font.Height = 12 * 20;

            foreach (var item in rows)
            {
                r++;
                for (int i = 0; i < item.Value.Length; i++)
                {
                    cells.Add(r, (i + 1), item.Value[i], xf);
                }
            }
            //doc.Save(resp.OutputStream);
            doc.Save(filePath, true);
            return fileName;
        }

        /// <summary>
        /// 导出报表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="caption"></param>
        /// <param name="list"></param>
        public static void ExportDataSetToFile<T>(string[] caption,List<T> list,string fileName)
        {
            HttpResponse resp;
            resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            resp.HeaderEncoding = System.Text.Encoding.GetEncoding("UTF-8"); //!important
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            worksheet.Cells.ImportCustomObjects(list, caption, false, 1, 0, list.Count, true, "", false);
            System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流 
            byte[] bt = ms.ToArray();

            //以字符流的形式下载文件  

            resp.ContentType = "application/vnd.ms-excel";

            //通知浏览器下载文件而不是打开
            resp.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            resp.BinaryWrite(bt);

            resp.Flush();
            resp.End();
        }

        public static void ExportDataSetToFile<T>(string[] caption, List<T> list,string[] flied, string fileName)
        {
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;
            cells.InsertRow(0);
            Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
            style.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
            style.Font.Size = 11;//文字大小  
            style.Font.IsBold = true;//粗体 
            cells.SetRowHeight(0, 20);              //设置行高 
            for (int i = 0; i < caption.Length; i++)
            {
                cells[0, i].PutValue(caption[i]);
                cells[0, i].SetStyle(style);
                cells.SetColumnWidth(i, 30);
            }
            worksheet.Cells.ImportCustomObjects(list, flied, false, 1, 0, list.Count, true, "yyyy-MM-dd HH:mm:ss", false);
            workbook.Save(System.Web.HttpContext.Current.Response, fileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));
        }
    }
}
